import java.sql.*;
import java.util.*;

import javax.imageio.ImageIO;
import javax.swing.ImageIcon;
import javax.swing.JOptionPane;

import java.awt.Image;
import java.awt.Toolkit;
import java.awt.image.BufferedImage;
import java.io.*;

class PersonsDAO {
	Connection con;
	private ArrayList<PersonalInfo> personList = new ArrayList<PersonalInfo>();

	public PersonsDAO() {
		getOracleConnection();
	}

	public Connection getOracleConnection() {
		String url = "jdbc:odbc:orcl";
		String user = "hr";
		String pass = "hr";

		try {
			Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
		} catch (java.lang.ClassNotFoundException e) {
			System.err.println("ClassNotFoundException :" + e.getMessage());
		}

		try {
			con = DriverManager.getConnection(url, user, pass);
		} catch (SQLException ex) {
			System.err.println("Sqlexception: " + ex.getMessage());
		}
		return con;
	}

	public ArrayList<PersonalInfo> searchPerson(String name) {
		name = name.trim();

		if (name.length() == 0) {
			System.out.println("Fill the name field!");

		}
		int id;
		double salary = 0;
		String firstName = "";
		String lastName = "";
		String email = "";
		ImageIcon icon=null;
		InputStream binaryStream = null;
		
		String selectString = "Select * from employees where last_Name like '%"
				+ name + "%'";
		try {
			Statement stmt = con.createStatement();
			ResultSet rs = stmt.executeQuery(selectString);
			while (rs.next()) {
				id = rs.getInt("employee_id");
				firstName = rs.getString("first_name");
				lastName = rs.getString("last_name");
				salary = rs.getDouble("salary");
				email = rs.getString("email");
			//	binaryStream = rs.getBinaryStream("image");
				//
				byte[] imageBytes = rs.getBytes("IMAGE");
				if (imageBytes != null) {
					/*
					 * image =
					 * Toolkit.getDefaultToolkit().createImage(imageBytes);
					 */
					icon = new ImageIcon(imageBytes);
				} else {
					icon = null;
				}
				
				
				Image image = null;
				//InputStream bin = imageBlob.getBinaryStream(0, imageBlob.length());
			
				//BufferedImage bfImage = ImageIO.read(bin);
				//
				
				PersonalInfo person = new PersonalInfo(id, firstName, lastName,
						salary, email, image, icon);
				personList.add(person);
			}

		} catch (Exception ex) {
			System.err.println("exception: " + ex.getMessage());
		}
		return personList;
	}

	public int updatePerson(int id, String firstName,
			String lastName, String salaryStr, String email, byte[] bytesImg) {
        double salary;
        salary = Double.parseDouble(salaryStr);
        
		PreparedStatement updateStatement = null;
        int rowCount =0;
		String updateString = "update hr.employees "
				+ "set first_name = ?, last_name = ?,  salary =?, email = ?, image = ?"
				+ "where employee_id = ?";
		try {
			con.setAutoCommit(false);
			updateStatement = con.prepareStatement(updateString);
			updateStatement.setString(1, firstName);
			updateStatement.setString(2, lastName);
			updateStatement.setDouble(3, salary);
			updateStatement.setString(4, email);
			
			//FileInputStream finImg = new FileInputStream(imgFile);
			//updateStatement.setBinaryStream(5, finImg, (int)imgFile.length());
			updateStatement.setBytes(5, bytesImg);
			
			updateStatement.setInt(6, id);
			rowCount = updateStatement.executeUpdate();
			con.commit();
		} catch (Exception e) {
			System.err.println("SQL exception: " + e.getMessage());
			if (con != null) {
				System.err.println("Transaction is being rolled back");
				try {
					con.rollback();
				} catch (SQLException eRoll) {
					System.err.println("SQL exception: " + eRoll.getMessage());
				}
			}
		} finally {

			try {
				if (updateStatement != null) {
					updateStatement.close();
				}
				con.setAutoCommit(true);
			} catch (SQLException exClose) {
				System.err.println("SQL exception: " + exClose.getMessage());
			}
		}

		return rowCount;
	}

	public int savePerson(String firstName,
			String lastName, String salaryStr, String email) {
        double salary;
        salary = Double.parseDouble(salaryStr);
        		
		PreparedStatement updateStatement = null;
        int rowCount =0;
		String updateString = "insert into hr.employees "
				+"(employee_id,first_name, last_name, salary, email, hire_date, job_id)"
				+"values(EMPLOYEES_SEQ.nextval,?,?,?,?,'12-may-00', 'PU_MAN')";
		try {
			con.setAutoCommit(false);
			updateStatement = con.prepareStatement(updateString);
			updateStatement.setString(1, firstName);
			updateStatement.setString(2, lastName);
			updateStatement.setDouble(3, salary);
			updateStatement.setString(4, email);
			rowCount = updateStatement.executeUpdate();
			con.commit();
		} catch (SQLException e) {
			System.err.println("SQL exception: " + e.getMessage());
			if (con != null) {
				System.err.println("Transaction is being rolled back");
				try {
					con.rollback();
				} catch (SQLException eRoll) {
					System.err.println("SQL exception: " + eRoll.getMessage());
				}
			}
		} finally {

			try {
				if (updateStatement != null) {
					updateStatement.close();
				}
				con.setAutoCommit(true);
			} catch (SQLException exClose) {
				System.err.println("SQL exception: " + exClose.getMessage());
			}
		}

		return rowCount;
	}
	
	public int deletePerson(int id) {
		if(id==0){
			JOptionPane.showMessageDialog(null, "Please, choose any person");
			return 0;
		}
		PreparedStatement updateStatement = null;
        int rowCount =0;
		String updateString = "delete from hr.employees "
				+ "where employee_id = ?";
		try {
			con.setAutoCommit(false);
			updateStatement = con.prepareStatement(updateString);
			updateStatement.setInt(1, id);
			rowCount = updateStatement.executeUpdate();
			con.commit();
		} catch (SQLException e) {
			System.err.println("SQL exception: " + e.getMessage());
			if (con != null) {
				System.err.println("Transaction is being rolled back");
				try {
					con.rollback();
				} catch (SQLException eRoll) {
					System.err.println("SQL exception: " + eRoll.getMessage());
				}
			}
		} finally {

			try {
				if (updateStatement != null) {
					updateStatement.close();
				}
				con.setAutoCommit(true);
			} catch (SQLException exClose) {
				System.err.println("SQL exception: " + exClose.getMessage());
			}
		}

		return rowCount;
	}
}
